/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package r2x;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import java.util.LinkedList;
import java.util.ListIterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTextArea;

/**
 *
 * @author przemek
 */
public class DTDCreator {

    public static String REFERENCE_INDICATOR = "REFERENCES";
    public static String PRIMARY_KEY_INDICATOR = "PRIMARY KEY";
    public static String ZEROORMORE = "*";
    public static String EXACTLYONE = "";
    public static String ONEORMORE = "+";
    public static String ZEROORONE = "?";

    private JTextArea output;
    private DTDFile dtdFile;
    private DTDElement dtdRootElement;
    private String destination;

    public DTDCreator() {
    }

    public void createDTD() {
//saying hello etc.
        getOutput().append("starting to create DTD\n");

//obtaining data
        getTabNames();
        analyseReferences();
        getColumnsForEveryTable();
        assignTypesOfTables();
        findPrimaryKeysInTables();
        identifyFact();

//preparing tree
        createRoot();
        createElementsTree();

//creating text
        dtdFile = new DTDFile();
        dtdFile.updateContent(getDtdRootElement());
        dtdFile.closeFile();

//printing results, saying goodbye
//        getOutput().append("" + R2XApp.getApplication().getDbManager().tabInfoListoToString());
//        getOutput().append("\n\n> > > > > GENERATED DTD < < < < <\n" + dtdFile.toString());
        getOutput().append("end of creating DTD\n");
        try{
        printToDestination();
        getOutput().append("DTD saved to " + destination +"\n");
        }
        catch (Exception e)
        {
            getOutput().append("Can't save to file "+destination+"!!!!!!!!!!!!\n");
            e.printStackTrace();
        }
    }

    private void assignTypesOfTables() {
        TabInfo el;
        LinkedList<TabInfo> tabList = R2XApp.getApplication().getDbManager().getTabList();
        for (int i = 0; i < tabList.size(); ++i) {
            el = tabList.get(i);
            if (el.isStandAloneTable()) {
                el.setType(TabInfo.STAND_ALONE);
            } else if (el.isNNRelationTable()) {
                el.setType(TabInfo.NNRELATION);
            } else {
            }
        }

    }

    private void createRoot() {
        try {
            setDtdRootElement(new DTDElement(R2XApp.getApplication().getDbManager().getDbName(), EXACTLYONE));
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void createElementsTree() {
        addStandAloneTables();
        prepareFactElementSkeleton();
        getRidOfNNRelationsAndHandleWithRelatedElements();
        addTabsOfTypeAsElementsToElement(TabInfo.NC,getDtdRootElement());
    }

    private void addStandAloneTables() {
        LinkedList<TabInfo> selectedNodesList = new LinkedList<TabInfo>();
        LinkedList<TabInfo> tabList = R2XApp.getApplication().getDbManager().getTabList();
        for (int i = 0; i < tabList.size(); i++) {
            if (tabList.get(i).isStandAloneTable()) {
                selectedNodesList.add(tabList.get(i));
            }
        }

        for (int i = 0; i < selectedNodesList.size(); i++) {
            getDtdRootElement().getChildrenList().add(new DTDElement(selectedNodesList.get(i)));
        }
    }

//TODO: not tested for complex primary keys
    private void findPrimaryKeysInTables() {
        DBManager dbMan = R2XApp.getApplication().getDbManager();
        LinkedList<TabInfo> tabList = dbMan.getTabList();
        LinkedList<String> primColumnsNamesList;
        LinkedList<ColumnInfo> colList;
        TabInfo tempTab;
        ColumnInfo tempCol;

        try {
            Statement s = dbMan.getConn().createStatement();
            //perform show statement for every table....
            for (int i = 0; i < tabList.size(); ++i) {
                tempTab = tabList.get(i);
                s.executeQuery("SHOW CREATE TABLE " + tempTab.getTabName());
                ResultSet rs = s.getResultSet();
                while (rs.next()) {
                    String dupa = rs.getString("Create Table");
                    //...get list of names of columns that are in the primary key for that table...
                    primColumnsNamesList = Tools.getNamesAfterSubstring(dupa,
                            PRIMARY_KEY_INDICATOR);
                    tempTab.setPrimKeyNames(primColumnsNamesList);
                    colList = tempTab.getColumnsList();
                    //...for every column of that table...
                    for (int j = 0; j < colList.size(); j++) {
                        //...check if it's name is in the list of names of primary keys
                        for (int k = 0; k < primColumnsNamesList.size(); k++) {
                            String pcName = primColumnsNamesList.get(k);
                            if (colList.get(j).getName().equals(pcName)) {
                                colList.get(j).setInPrimKey(true);
                                break;
                            }
                        }
                    }
                }
                rs.close();
            }
            s.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * This version gets rid of NN relations that are relating to the fact table
     * Implemented for tables which doesn't have complex primary keys (they just have
     * 2 foreign keys and nothing more)
     */
    private void getRidOfNNRelationsAndHandleWithRelatedElements() {
        LinkedList<TabInfo> tabList = R2XApp.getApplication().getDbManager().getTabList();
        TabInfo factTab = R2XApp.getApplication().getDbManager().getTabsOfType(TabInfo.CENTRAL).getFirst();
        ListIterator<TabInfo> it = tabList.listIterator();
        DTDElement factEl = getDtdRootElement().getChildByName(factTab.getTabName());
        TabInfo tab, nTab;
        String tempName, tempCard;
        while (it.hasNext()) {
            tab = it.next();
            if (tab.isNNRelationTable()) {
                    //first and last can be used, since there are only 2 elements for sure
                if (tab.getReferToList().getFirst().getBeingRefNumber()>=
                        tab.getReferToList().getLast().getBeingRefNumber())
                {
                    nTab = tab.getReferToList().getLast();
                } else {
                    nTab = tab.getReferToList().getFirst();
                }
                tempName = tab.getTabName();
                tempCard = DTDCreator.ZEROORMORE;
                try {
                    factEl.addChild(new DTDElement(tempName, tempCard, DTDElement.NNREL));
                } catch (Exception e) {
                    System.out.println(e);
                }
            }
        }
    }

    private void getTabNames() {
        try {
            DBManager dbMan = R2XApp.getApplication().getDbManager();
            Statement s = dbMan.getConn().createStatement();
            s.executeQuery("SHOW TABLES");
            ResultSet rs = s.getResultSet();
            int count = 0;
            while (rs.next()) {
                ++count;
                String tabName = rs.getString("Tables_in_" + dbMan.getDbName());
                dbMan.getTabList().add(new TabInfo(tabName));
            }
            R2XApp.getApplication().getDbManager().setTabNumber(count);
            rs.close();
            s.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void analyseReferences() {
        DBManager dbMan = R2XApp.getApplication().getDbManager();
        LinkedList<TabInfo> tabList = dbMan.getTabList();
        LinkedList<String> namesList;
        TabInfo el, tempEl;

        try {
            Statement s = dbMan.getConn().createStatement();
            for (int i = 0; i < tabList.size(); ++i) {
                el = tabList.get(i);
                s.executeQuery("SHOW CREATE TABLE " + el.getTabName());
                ResultSet rs = s.getResultSet();
                while (rs.next()) {
                    String dupa = rs.getString("Create Table");
                    namesList = Tools.getNamesAfterSubstring(dupa, REFERENCE_INDICATOR);
                    el.setRefNumber(namesList.size());
                    for (int j = 0; j < namesList.size(); j++) {
                        String tName = namesList.get(j);
                        tempEl = dbMan.getTabInfoElementByName(tName);
                        tempEl.setBeingRefNumber(tempEl.getBeingRefNumber() + 1);
                        el.getReferToList().add(tempEl);
                        tempEl.getReferredByList().add(el);
                    }
                }
                rs.close();
            }
            s.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private void getColumnsForEveryTable() {

        DBManager dbMan = R2XApp.getApplication().getDbManager();
        LinkedList<TabInfo> tabList = dbMan.getTabList();
        TabInfo el, tempEl;

        try {
            Statement s = dbMan.getConn().createStatement();
            for (int i = 0; i < tabList.size(); ++i) {
                el = tabList.get(i);
                s.executeQuery("SHOW COLUMNS FROM " + el.getTabName());
                ResultSet rs = s.getResultSet();
                int count = 0;
                while (rs.next()) {
                    String name = rs.getString("Field");
                    String req = rs.getString("Null");
                    el.getColumnsList().add(new ColumnInfo(name, req));
                    ++count;
                }
                rs.close();
                el.setColumnNumber(count);
            }
            s.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     *
     * This version of function supports only case in which there is only one
     * fact table in the database. Fact is recognized due to the superstition
     * that it is the table that is referred the most times by the other
     * tables and it does not refer to any table by itself.
     */
    private void identifyFact() {
        LinkedList<TabInfo> tabList = R2XApp.getApplication().getDbManager().getTabList();
        ListIterator<TabInfo> it = tabList.listIterator();
        TabInfo el, factCandidate = null;
        int maxRefBy = 0;
        while (it.hasNext()) {
            el = it.next();
            if ((el.getRefNumber() == 0) && (el.getBeingRefNumber() > maxRefBy)) {
                maxRefBy = el.getBeingRefNumber();
                factCandidate = el;
            }
        }
        if (factCandidate != null) {
            factCandidate.setType(TabInfo.CENTRAL);
        }
    }

    private void prepareFactElementSkeleton() {
        LinkedList<TabInfo> factsList =
                R2XApp.getApplication().getDbManager().getTabsOfType(TabInfo.CENTRAL);
        if (factsList.size() != 1) {
            System.out.println("Too many fact tables for this version of app\n");
            return;
        }
        DTDElement factEl = new DTDElement(factsList.getFirst());
        getDtdRootElement().getChildrenList().add(factEl);
    }

    private void addTabsOfTypeAsElementsToElement(int type, DTDElement el) {
        LinkedList<TabInfo> tabList = R2XApp.getApplication().getDbManager().getTabList();
        for (int i = 0; i < tabList.size(); i++) {
            if (tabList.get(i).getType() == type) {
            el.addChild(new DTDElement(tabList.get(i)));
            }
        }

    }

    /**
     * @return the output
     */
    public JTextArea getOutput() {
        return output;
    }

    /**
     * @param output the output to set
     */
    public void setOutput(JTextArea output) {
        this.output = output;
    }

    /**
     * @return the dtdRootElement
     */
    public DTDElement getDtdRootElement() {
        return dtdRootElement;
    }

    /**
     * @param dtdRootElement the dtdRootElement to set
     */
    public void setDtdRootElement(DTDElement dtdRootElement) {
        this.dtdRootElement = dtdRootElement;
    }

    /**
     * @return the destination
     */
    public String getDestination() {
        return destination;
    }

    /**
     * @param destination the destination to set
     */
    public void setDestination(String destination) {
        this.destination = destination;
    }

    private void printToDestination() throws IOException {
        File file = new File (destination);
        PrintWriter out = new PrintWriter(new FileWriter(file));
        out.print(dtdFile.toString());
        out.close();

    }
}
